//201902104003邵潇漩
package bysj.dao;

import bysj.domain.*;
import bysj.service.GraduateProjectCategoryService;
import bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class OpenAreaDao {
    private static OpenAreaDao openAreaDao = null;
    private OpenAreaDao() {
    }

    public static OpenAreaDao getInstance() {
        if (openAreaDao == null){
            openAreaDao = new OpenAreaDao();
        }
        return openAreaDao;
    }
    public static Collection<OpenArea> findAll(Connection connection , List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<OpenArea> openAreas = new TreeSet<OpenArea>();
        int totalNum = CommonDao.getInstance().count(connection,"OpenArea");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM OpenArea");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }

        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            openAreas.add(new OpenArea(resultSet.getInt("id"),
                    resultSet.getString("area"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    resultSet.getString("requirement"),
                    resultSet.getInt("positionNum"),
                            resultSet.getInt("year")
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return openAreas;
    }

    public OpenArea find(Connection conn,Integer id) throws SQLException {
        OpenArea desiredOpenArea = null;
        String sql_select_id="SELECT*FROM OpenArea WHERE id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql_select_id);
        pstmt.setInt(1,id);
        ResultSet resultSet=pstmt.executeQuery();
        if(resultSet.next()){
            String area=resultSet.getString("area");
            String requirement = resultSet.getString("requirement");
            int positionNum=resultSet.getInt("positionNum");
            int year=resultSet.getInt("year");
            int graduateProjectCategoryId=resultSet.getInt("graduateProjectCategory_id");
            int teacherId=resultSet.getInt("teacher_id");
            GraduateProjectCategory graduateProjectCategory= GraduateProjectCategoryService.getInstance().find(graduateProjectCategoryId);
            Teacher teacher= TeacherService.getInstance().find(teacherId);
            desiredOpenArea=new OpenArea(id,area,graduateProjectCategory,teacher,requirement,positionNum,year);
        }
        JdbcHelper.close(resultSet,pstmt);
        return desiredOpenArea;
    }
    public boolean update(Connection conn,OpenArea openArea) throws SQLException {
        //创建sql语句
        String updateOpenArea_sql = "UPDATE OpenArea SET area=?, requirement=?, positionNum=?, year=? , graduateProjectCategory_id=?, teacher_id=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(updateOpenArea_sql);
        // 为预编译参数赋值
        pstmt.setString(1, openArea.getArea());
        pstmt.setString(2, openArea.getRequirement());
        pstmt.setInt(3, openArea.getPositionNum());
        pstmt.setInt(4, openArea.getYear());
        pstmt.setInt(5,openArea.getGraduateProjectCategory().getId());
        pstmt.setInt(6,openArea.getTeacher().getId());
        pstmt.setInt(7,openArea.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }
    public boolean add(Connection conn, OpenArea openArea) throws SQLException {
        String addOpenArea_sql="INSERT INTO OpenArea "+"(area,requirement,positionNum,year,graduateProjectCategory_id,teacher_id)value(?,?,?,?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(addOpenArea_sql);
        //为预编译参数赋值
        pstmt.setString(1, openArea.getArea());
        pstmt.setString(2, openArea.getRequirement());
        pstmt.setInt(3, openArea.getPositionNum());
        pstmt.setInt(4, openArea.getYear());
        pstmt.setInt(5,openArea.getGraduateProjectCategory().getId());
        pstmt.setInt(6,openArea.getTeacher().getId());
        int rowAffected =pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return rowAffected>0;
    }
    public boolean delete(Connection conn,Integer id) throws SQLException {
        //创建sql语句
        String deleteOpenArea_sql = "DELETE FROM OpenArea WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteOpenArea_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }

    public boolean delete(Connection connection, OpenArea openArea) throws SQLException {
        return this.delete(connection,openArea.getId());
    }
}
